﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;



using ES_Project.Model;
using MySql.Data.MySqlClient;
using System.Data;
using System.Web.Configuration;
using System.Drawing;
using System.Data.SqlClient;
namespace ES_Project.Pages
{
    public partial class WebForm447 : System.Web.UI.Page
    {
        private List<DateTime> listDates;
        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand();
        #region "Page Load"
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                
               // BindData();
                //Calendar1.DayRender += new DayRenderEventHandler(Calendar1_DayRender);
                //listDates = new List<DateTime>();
                //listDates.Add(DateTime.Now);
                //listDates.Add(DateTime.Now.AddDays(1));

                //listDates.Add(DateTime.Now.AddDays(5)); 
            }
            
        }
        #endregion

        //void Calendar1_DayRender(object sender, DayRenderEventArgs e)
        //{
        //    //throw new Exception("The method or operation is not implemented.");
        //    if (e.Day.Date.Day == 5)//สมมุติวันที่ 5 นัดหมาย
        //    {
        //        e.Cell.BackColor = System.Drawing.Color.Red;
        //    }
        //}
        protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;




            //strSQL = "SELECT cc.calendar_id, cc.day, cc.holiday_date, cc.holiday_desc " +

            //         "FROM cls_calendar cc ";


            //Conn.Open();
            //MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            //DataSet ds4 = new DataSet();
            //da4.Fill(ds4);
            //da4 = null;
            //Conn.Close();
            //Conn = null;
            //listDates = new List<DateTime>();
            //int _row = ds4.Tables[0].Rows.Count;
            //int i = 0;
            //TextBox3.Text = _row.ToString();
            //if (_row > i)
            //{


            //    //listDates.Add(Convert.ToDateTime(ds4.Tables[0].Rows[i]["holiday_date"]));

            //    Calendar1.SelectedDates.Add(Convert.ToDateTime(ds4.Tables[0].Rows[i]["holiday_date"]));
            //    if (e.Day.IsSelected)
            //    {

            //        e.Cell.BackColor = System.Drawing.Color.Green;
            //        Calendar1.SelectedDayStyle.BackColor = System.Drawing.Color.Red;
            //        //e.Day.IsSelectable = true;
            //    }

            //}

            
            strSQL = "SELECT  cc.day, cc.holiday_date, cc.holiday_desc " +

                     "FROM cls_calendar cc ";
            //myCommand.CommandType = CommandType.Text;
            // Opens a Database Connection
            objCmd.CommandText = strSQL;
            objCmd.CommandType = CommandType.Text;
            // myConnection.Open();
            // Execute DataReader
            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();
            //SqlDataReader dr = myCommand.ExecuteReader();
            // Read DataReader till it reaches the end
            while (dtReader.Read() == true)
            {
                 
                Calendar1.SelectedDates.Add(dtReader.GetDateTime(1));
                    //listDates.Add(Convert.ToDateTime(ds4.Tables[0].Rows[i]["holiday_date"]));

                    //Calendar1.SelectedDates.Add(Convert.ToDateTime(ds4.Tables[0].Rows[i]["holiday_date"]));
                    if (e.Day.IsSelected)
                    {

                        e.Cell.BackColor = System.Drawing.Color.Green;
                        
                        //e.Day.IsSelectable = true;
                    }
                //Calendar1.SelectedDayStyle.BackColor = System.Drawing.Color.Red;
                //Calendar1.SelectedDates.Add((DateTime)dtReader.GetSqlDateTime(0));
            }

            // Close DataReader
            dtReader.Close();
            // Close database Connection
            //Calendar1.SelectedDayStyle.BackColor = System.Drawing.Color.Red;
            Conn.Close();
            
            //foreach (DateTime d in listDates)
            //{
            //    //Calendar1.SelectedDayStyle.AddAttributesToRender(d);
                
            //    Calendar1.SelectedDates.Add(d);
            //    if (e.Day.IsSelected)
            //    {

            //        e.Cell.BackColor = System.Drawing.Color.Green;
            //        //e.Day.IsSelectable = true;
            //    }
            //}
        } 

        protected void calendar_SelectionChanged(object sender, EventArgs e) 
        {

            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;

            //TextArea1.Value = "";
            TextBox1.Text = "";

            TextBox2.Text = "";
            //calendar_id.Text = "";
            //TextBox3.Text = toDayOfThai(Calendar1.SelectedDate.DayOfWeek.ToString());


            strSQL = "SELECT  cc.day, cc.holiday_date, cc.holiday_desc " +

                     "FROM cls_calendar cc " +
                     "where cc.holiday_date = '" + toDate(Calendar1.SelectedDate.ToShortDateString()) + "' ";


            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;

            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            //TextBox3.Text = _row.ToString();
            if (_row > i)
            {


                TextArea1.Value = (string)ds4.Tables[0].Rows[i]["holiday_desc"].ToString();
                TextBox1.Text = Convert.ToDateTime((string)ds4.Tables[0].Rows[i]["holiday_date"].ToString()).ToShortDateString();

                TextBox2.Text = (string)ds4.Tables[0].Rows[i]["day"].ToString();
                //calendar_id.Text = (string)ds4.Tables[0].Rows[i]["calendar_id"].ToString();
                //Calendar1.SelectedDates.Add(Convert.ToDateTime(ds4.Tables[0].Rows[i]["holiday_date"]));


            }
            else
            {
                TextBox1.Text = Calendar1.SelectedDate.ToShortDateString();

                TextBox2.Text = toDayOfThai(Calendar1.SelectedDate.DayOfWeek.ToString());
                TextArea1.Value = "";
            }
            
        }

        #region "Save and Edit to add value for calendar table"
        protected void CMD_INSERT_Click(object sender, EventArgs e)
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                Button bt = (Button)sender;
                switch (bt.ID)
                {
                    case "CMD_INSERT":

                        cls_calendar c = new cls_calendar();
                        c.day = TextBox2.Text;
                        if (!TextBox1.Text.Equals(""))
                        {
                            c.holiday_date = Convert.ToDateTime(TextBox1.Text);
                        }
                        
                        c.holiday_desc = TextArea1.Value;
                        //c.holiday_desc = 
                        c.create_by = "Tong";
                        c.create_date = DateTime.Now;

                        
                        //pcm.emp_id = Convert.ToInt32(Request.QueryString["emp_id"]);
                        context.cls_calendar.AddObject(c);
                        try
                        {
                            if (context.SaveChanges() > 0)
                            {

                                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('เพิ่มข้อมูลเรียบร้อยแล้ว')", true);
                                //GridView4.DataBind();
                                TextBox1.Text = "";
                                TextArea1.Value = "";
                                TextBox2.Text = "";
                                //BindData();
                                //BindDataPay();
                                //GridView11.SelectedIndex = -1;
                                //GridView12.SelectedIndex = -1;
                                //CMD_INSERT.Enabled = true;
                                //CMD_EDIT.Enabled = false;
                                //clsRegCode.Text = "";
                                //clsName.Text = "";
                                //payId.Text = "";
                                //clsCourseTime.Text = "";
                                //clsCourseAmt.Text = "";
                                //clsCourseNo.Text = "";

                            }
                        }
                        catch (Exception ex)
                        {
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert6", "alert('การชำระเงินซ้ำ')", true);
                        }
                        break;
                    case "CMD_EDIT":


                        MySqlConnection objConn = new MySqlConnection();
     

                        objConn = new MySqlConnection(StrConn);
                        objConn.Open();
                        string strSQL;
                        strSQL = "UPDATE cls_calendar cc SET " +
                        "  cc.day = '" + TextBox2.Text + "' " +
                        " ,cc.holiday_desc = '" + TextArea1.Value + "' " +
                        " ,cc.update_date = '"+toDate(DateTime.Now.ToShortDateString())+"' "+
                        " ,cc.update_by = 'Tong' "+
                        " WHERE cc.holiday_date = '" + toDate(TextBox1.Text) + "' ";

                        MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, objConn);
                        //objCmd = new MySqlCommand();
                        objCmd.Connection = objConn;
                        objCmd.CommandText = strSQL;
                        objCmd.CommandType = CommandType.Text;

                        objCmd.ExecuteNonQuery();
                        ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert5", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                        TextBox1.Text = "";
                        TextArea1.Value = "";
                        TextBox2.Text = "";

                        //List<cls_calendar> crm1 = context.cls_calendar.Where("it.[holiday_date]= '" + toDate(TextBox1.Text) + "' ").ToList();
                        //if (crm1.Count() == 1)
                        //{
                        //    cls_calendar crmEdit1 = context.cls_calendar.Where("it.[holiday_date]= '" + toDate(TextBox1.Text) + "' ").First();
                        //    if (crmEdit1 != null)
                        //    {
                        //        crmEdit1.update_by = "Tong";
                        //        crmEdit1.update_date = DateTime.Now;
                        //        crmEdit1.day= TextBox2.Text;
                        //        if (!TextBox1.Text.Equals(""))
                        //        {
                        //            crmEdit1.holiday_date = Convert.ToDateTime(TextBox1.Text);
                        //        }

                        //        crmEdit1.holiday_desc = TextArea1.Value;
                        //        //crmEdit.cls_course_time = clsCourseTime.Text;
                        //        //crmEdit.cls_course_amt = clsCourseAmt.Text;
                        //        //crmEdit1.pay_id = Convert.ToInt32(pay_id.Text);
                        //        //crmEdit.cls_course_no = clsCourseNo.Text;


                        //        if (context.SaveChanges() > 0)
                        //        {

                        //            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                        //            TextBox1.Text = "";
                        //            TextArea1.Value = "";
                        //            TextBox2.Text = "";
                        //            //GridView4.DataBind();
                        //            //BindData();
                        //            //GridView11.SelectedIndex = -1;
                        //            //CMD_INSERT.Enabled = true;
                        //            //CMD_EDIT.Enabled = false;
                        //            //clsRegCode.Text = "";
                        //            //clsName.Text = "";
                        //            //pay_id.Text = "";
                        //            //clsCourseTime.Text = "";
                        //            //clsCourseAmt.Text = "";
                        //            //clsCourseNo.Text = "";

                        //        }
                        //    }
                        //}
                        break;
                }
            }
        }
        #endregion

        #region "hilight on calendar"
        void hilight()
        {
            //CMD_INSERT.Enabled = false;
            //CMD_EDIT.Enabled = true;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;




            strSQL = "SELECT  cc.day, cc.holiday_date, cc.holiday_desc " +
                     
                     "FROM cls_calendar cc ";


            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;
            listDates = new List<DateTime>();
            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            
            if (_row > i)
            {
                

                listDates.Add(Convert.ToDateTime(ds4.Tables[0].Rows[i]["holiday_date"]));
                //Calendar1.SelectedDates.Add(Convert.ToDateTime(ds4.Tables[0].Rows[i]["holiday_date"]));

                
            }
        }
        #endregion

         void BindData()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;
            strSQL = "SELECT  cc.day, cc.holiday_date, cc.holiday_desc " +

                     "FROM cls_calendar cc ";
            //myCommand.CommandType = CommandType.Text;
            // Opens a Database Connection
             objCmd.CommandText = strSQL;
             objCmd.CommandType = CommandType.Text;
           // myConnection.Open();
            // Execute DataReader
            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();
            //SqlDataReader dr = myCommand.ExecuteReader();
            // Read DataReader till it reaches the end
            while (dtReader.Read() == true)
            {
                dtReader.GetDateTime(2);
                // Assign the Calendar control dates
                // already contained in the database
                //Calendar1.SelectedDates.Add((dtReader.));
               // Calendar1.SelectedDayStyle.BackColor = System.Drawing.Color.Red;
                //Calendar1.SelectedDates.Add((DateTime)dtReader.GetSqlDateTime(0));
            }

            // Close DataReader
            dtReader.Close();
            // Close database Connection
            //Calendar1.SelectedDayStyle.BackColor = System.Drawing.Color.Red;
            Conn.Close();
        }

         public static String toDate(String date)
         {
             String day = date.Split('/')[0];
             String month = date.Split('/')[1];
             String year = (Convert.ToInt32(date.Split('/')[2]) - 543).ToString();

             //DateTime dateForMat = DateTime.Parse((day + "/" + month + "/" + year));
             //String dateUs = string.Format("{0:yyyy/MM/dd HH:mm:ss}", dateForMat);
             //return dateUs;
             return string.Format("{0:yyyy/MM/dd HH:mm:ss}", DateTime.Parse((day + "/" + month + "/" + year)));
         }
         public static String toDateNotMin(String date)
         {
             String day = date.Split('/')[0];
             String month = date.Split('/')[1];
             String year = (Convert.ToInt32(date.Split('/')[2]) - 543).ToString();

             //DateTime dateForMat = DateTime.Parse((day + "/" + month + "/" + year));
             //String dateUs = string.Format("{0:yyyy/MM/dd HH:mm:ss}", dateForMat);
             //return dateUs;
             return string.Format("{0:yyyy/MM/dd}", DateTime.Parse((day + "/" + month + "/" + year)));
         }
         public static String toDayOfThai(String DayOfThai)
         {
             if (DayOfThai.Equals("Monday")) {
                 return "วันจันทร์";
             }
             else if (DayOfThai.Equals("Tuesday")) {
                 return "วันอังคาร";
             }
             else if (DayOfThai.Equals("Wednesday"))
             {
                 return "วันพุธ";
             }
             else if (DayOfThai.Equals("Thursday"))
             {
                 return "วันพฤหัสบดี";
             }
             else if (DayOfThai.Equals("Friday"))
             {
                 return "วันศุกร์";
             }
             else if (DayOfThai.Equals("Saturday"))
             {
                 return "วันเสาร์";
             }
             else if (DayOfThai.Equals("Sunday"))
             {
                 return "วันอาทิตย์";
             }
             return "55";
         }
         protected void CMD_CLEAR_Click(object sender, EventArgs e)
         {
             TextBox1.Text = "";
             TextBox2.Text = "";
             TextArea1.Value = "";
             
         }
    }
}